﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using CLEX.DataContracts;

namespace CLEX.Server.DAL
{
    public sealed class FriendshipDAL : IFriendshipDAL
    {
        private const string USER_1_ID = "user_1_id";
        private const string USER_2_ID = "user_2_id";

        private const string GET_ALL_SP = "friendship_get_all";
        private const string GET_SP = "friendship_get";
        private const string CREATE_SP = "friendship_create";
        private const string UPDATE_SP = "friendship_update";
        private const string DELETE_SP = "friendship_delete";

        private readonly string connectionString;

        public FriendshipDAL(string connectionString)
        {
            this.connectionString = connectionString;
        }

        public List<FriendshipData> GetAll()
        {
            List<FriendshipData> result = new List<FriendshipData>();

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(GET_ALL_SP, conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        int user1IdCol = reader.GetOrdinal(USER_1_ID);
                        int user2IdCol = reader.GetOrdinal(USER_2_ID);

                        while (reader.Read())
                        {
                            result.Add(new FriendshipData()
                            {
                                User1Id = reader.GetInt32(user1IdCol),
                                User2Id = reader.GetInt32(user2IdCol),
                            });
                        }
                    }
                }
            }

            return result;
        }

        public FriendshipData Get(int user1Id, int user2Id)
        {
            FriendshipData result;

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(GET_SP, conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue(USER_1_ID, user1Id);
                    cmd.Parameters.AddWithValue(USER_2_ID, user2Id);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        int user1IdCol = reader.GetOrdinal(USER_1_ID);
                        int user2IdCol = reader.GetOrdinal(USER_2_ID);

                        reader.Read();
                        result = new FriendshipData()
                        {
                            User1Id = reader.GetInt32(user1IdCol),
                            User2Id = reader.GetInt32(user2IdCol),
                        };
                    }
                }
            }

            return result;
        }

        public FriendshipData Create(FriendshipData friendship)
        {
            FriendshipData result = new FriendshipData();

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(CREATE_SP, conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue(USER_1_ID, friendship.User1Id);
                    cmd.Parameters.AddWithValue(USER_2_ID, friendship.User2Id);
                    cmd.ExecuteNonQuery();
                }
            }

            result.User1Id = friendship.User1Id;
            result.User2Id = friendship.User2Id;
            return result;
        }

        public void Update(FriendshipData friendship)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(UPDATE_SP, conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue(USER_1_ID, friendship.User1Id);
                    cmd.Parameters.AddWithValue(USER_2_ID, friendship.User2Id);
                    cmd.ExecuteNonQuery();
                }
            }
        }

        public void Delete(FriendshipData friendship)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(DELETE_SP, conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue(USER_1_ID, friendship.User1Id);
                    cmd.Parameters.AddWithValue(USER_2_ID, friendship.User2Id);
                    cmd.ExecuteNonQuery();
                }
            }
        }
    }
}
